package ghr.day0109.lottery.dao.impl;

import ghr.day0109.lottery.dao.UserDao;
import ghr.day0109.lottery.entity.UserMark;
import ghr.day0109.lottery.utils.BaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserDaoimpl extends BaseDao implements UserDao {
    @Override
    public List<UserMark> queryAllUser() {
        List<UserMark> userMarkList = new ArrayList<>();
        UserMark userMark = null;
        String sql =  "select id, user_name, mark_no, status, phone, to_char(open_date,'yyyy-mm-dd') open_date from user_mark order by open_date";
        super.executeQuery(sql);
        try{while(rs.next()){
            userMark = new UserMark();
            userMark.setId(rs.getInt("id"));
            userMark.setUserName(rs.getString("user_name"));
            userMark.setMarkNo(rs.getString("mark_no"));
            userMark.setStatus(rs.getInt("status"));
            userMark.setPhone(rs.getString("phone"));
            userMark.setOpenDate(rs.getString("open_date"));
            userMarkList.add(userMark);
        }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs,pstmt,conn);
        }
        return userMarkList;
    }

    @Override
    public List<UserMark> queryChooseUser(String select) {
        List<UserMark> userMarkList = new ArrayList<>();
        UserMark userMark = null;
        String sql = "select id, user_name, mark_no, status, phone, to_char(open_date,'yyyy-mm-dd') open_date from user_mark \n" +
                "where mark_no like ? ";
        super.executeQuery(sql,new Object[]{select});
        try{while(rs.next()){
            userMark = new UserMark();
            userMark.setId(rs.getInt("id"));
            userMark.setUserName(rs.getString("user_name"));
            userMark.setMarkNo(rs.getString("mark_no"));
            userMark.setStatus(rs.getInt("status"));
            userMark.setPhone(rs.getString("phone"));
            userMark.setOpenDate(rs.getString("open_date"));
            userMarkList.add(userMark);
        }
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs,pstmt,conn);
        }
        return userMarkList;
    }

    @Override
    public List<UserMark> queryChooseMode(Map<String, Object> map) {
        List<UserMark> userLists = new ArrayList<>();
        UserMark user = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ID,USER_NAME,MARK_NO,STATUS,PHONE,to_char(open_date,'yyyy-mm-dd') open_date FROM user_mark");
        sql.append(" WHERE 1 = 1 ");
        Object markNo = map.get("userMarkNo");
        Object status = map.get("userStatus");
        if ((!"".equals(markNo) & markNo != null) & status == null) {
            sql.append(" AND MARK_NO LIKE ? ");
            sql.append(" ORDER BY ID ");
            markNo = "%" + markNo + "%";
            rs = super.executeQuery(sql.toString(), new Object[]{markNo});
        } else if ((!"".equals(status) & status != null) & markNo == null) {
            sql.append(" ORDER BY ID ");
            sql.append(" AND STATUS = ? ");
            rs = super.executeQuery(sql.toString(), new Object[]{status});
        } else {
            sql.append(" ORDER BY ID ");
            rs = super.executeQuery(sql.toString());
        }

        try {
            while (rs.next()) {
                user = new UserMark();
                user.setId(rs.getInt("ID"));
                user.setUserName(rs.getString("USER_NAME"));
                user.setMarkNo(rs.getString("MARK_NO"));
                user.setStatus(rs.getInt("STATUS"));
                user.setPhone(rs.getString("PHONE"));
                user.setOpenDate(rs.getString("OPEN_DATE"));
                userLists.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs,pstmt,conn);
        }
        return userLists;

    }

    @Override
    public int addUser(UserMark user) {
        String sql ="insert into user_mark (id, user_name, mark_no, status, phone, open_date) values \n" +
                "(user_id.nextval, ?, ?, 1 ,?, To_date(?,'YYYY-MM-DD'))";
        return super.executeUpdate(sql,new Object[]{user.getUserName(),user.getMarkNo(),user.getPhone(),user.getOpenDate()});
    }

    @Override
    public int deleteUser(String name) {
        String sql ="delete from user_mark where user_name = ?";
        return super.executeUpdate(sql,new Object[]{name});

    }


}
